package com.eascs.web.o2o.web.bi.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.web.bi.dao.BIDao;
import com.eascs.web.o2o.web.bi.entity.BICompensatory;
import com.eascs.web.o2o.web.bi.entity.BICompensatoryDetail;
import com.eascs.web.o2o.web.bi.vo.LoanDetailForm;

/**
 * 代偿
 * 
 * @author ycz
 *
 */
@Service
public class CompensatoryService {
	@Autowired
	private BIDao biDao;

	public List<BICompensatory> queryCompensatory() {
		String sql = "SELECT o.regional_name AS provincial,	sum(l.amount) AS compensatoryBalance FROM"
				+ "	O2O_PLATFORM_REGIONAL o LEFT JOIN (	SELECT	t.ltd,	SUM(IFNULL(t.pay_amount, 0) - IFNULL(t.payment_amount, 0)"
				+ "	) AS amount	FROM o2o_ea_loan_data t	WHERE t.loan_type = 'DC'	GROUP BY t.ltd) l ON o.platform_id = l.ltd"
				+ " GROUP BY o.regional_name";
		return biDao.findEntitys(sql, BICompensatory.class);
	}

	/**
	 * 代偿明细
	 * 
	 * @param LoanDetailForm
	 * @return
	 */
	public List<BICompensatoryDetail> findcompensatoryDetailByPage(LoanDetailForm loanDetailForm) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				" SELECT oeld.id AS physicalId, oeld.bill_code_ea AS billCode, oll.loan_id AS customerId, CONCAT( LEFT (oeld.loaner_name, 1), '**' ) AS customerName, opr.province_name AS provincial, CONCAT( oll.ltd_code, '-', oll.ltd_name ) AS ltd, o.item_name AS loanBank, ( SELECT item_name FROM pub_dditem WHERE dd_no = '11321' AND item_code = oll.loan_type ) AS loanType, ( SELECT o.item_name FROM pub_dditem_o2o o WHERE dd_no = 'lhdch77b1ac1d' AND o.item_code = oeld.loan_type ) AS compensatoryType, oeld.pay_amount AS compensatoryAmount, DATE_FORMAT(oeld.pay_date, '%Y-%m-%d') AS compensatoryDate, IFNULL(oeld.payment_amount, 0) AS paymentAmount FROM o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply oll ON oll.bill_code = oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr ON oll.ltd = opr.platform_id LEFT JOIN o2o_ea_loan_settle oels ON oeld.id = oels.loan_data_id LEFT JOIN ( SELECT o.item_name, o.item_code FROM pub_dditem o WHERE o.dd_no = 10340 GROUP BY o.item_code ) o ON oll.bank_loan_name = o.item_code WHERE oeld.pay_type = 'DF' ");

		if (StringUtil.hasText(loanDetailForm.getStartDate())) {
			sql.append(" and oeld.pay_date >= ");
			sql.append("'");
			sql.append(loanDetailForm.getStartDate());
			sql.append("'");
		}
		if (StringUtil.hasText(loanDetailForm.getEndDate())) {
			sql.append(" and oeld.pay_date <  ");
			sql.append("'");
			sql.append(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(loanDetailForm.getEndDate()), 1)));
			sql.append("'");
		}

		sql.append(" order by oeld.pay_date desc ");
		return biDao.findEntitys(sql.toString(),BICompensatoryDetail.class);
	}
}
